package com.sinotrans.common.util.excel;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * <b>excel读取工具类</b>
 * 
 * @author gaofu
 *
 */
public class PoiXlsReader {

	private SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	/**
	 * 读取excel第一个sheet内容
	 * 
	 * @param excelFile
	 * @return
	 */
	public List<List<String>> readExcel(String excelFile) {
		return readExcel(excelFile, 0);
	}

	/**
	 * 读取excel
	 * 
	 * @param excelFile
	 * @param sheetNo   sheet序号, 从0开始
	 * @return
	 */
	public List<List<String>> readExcel(String excelFile, int sheetNo) {
		Sheet sheet = null;
		Row row = null;
		List<List<String>> list = null;
		String cellData = null;
		Workbook wb = null;
		if (excelFile == null) {
			return null;
		}
		InputStream is = null;
		try {
			is = new FileInputStream(excelFile);
			if (excelFile.endsWith(".xls")) {
				wb = new HSSFWorkbook(is);
			} else if (excelFile.endsWith(".xlsx")) {
				wb = new XSSFWorkbook(is);
			} else {
				wb = null;
			}
			if (wb != null) {
				// 用来存放表中数据
				list = new ArrayList<List<String>>();
				// 获取第一个sheet
				sheet = wb.getSheetAt(sheetNo);
				// 获取最大行数
				int rownum = sheet.getPhysicalNumberOfRows();
				for (int i = 0; i < rownum; i++) {
					List<String> cells = new ArrayList<String>();
					row = sheet.getRow(i);
					if (row != null) {
						int colnum = row.getPhysicalNumberOfCells();
						for (int j = 0; j < colnum; j++) {
							cellData = formatCellValue(row.getCell(j));
							cells.add(cellData);
						}
					} else {
						break;
					}
					list.add(cells);
				}
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 获取单个单元格数据
	 * 
	 * @param cell
	 * @return
	 * @author lizixiang ,2018-05-08
	 */
	private String formatCellValue(Cell cell) {
		String cellValue = null;
		if (cell != null) {
			// 判断cell类型
			CellType cellType = cell.getCellType();
			if (cellType == CellType.NUMERIC) {
				cellValue = String.valueOf(cell.getNumericCellValue());
			}
			if (cellType == CellType.FORMULA) {
				// 判断cell是否为日期格式
				if (DateUtil.isCellDateFormatted(cell)) {
					// 转换为日期格式YYYY-mm-dd
					cellValue = dateFormat.format(cell.getDateCellValue());
				} else {
					// 数字
					cellValue = String.valueOf(cell.getNumericCellValue());
				}
			}
			if (cellType == CellType.STRING) {
				cellValue = cell.getRichStringCellValue().getString();
			}
		} else {
			cellValue = "";
		}
		return cellValue;
	}

	public SimpleDateFormat getDateFormat() {
		return dateFormat;
	}

	public void setDateFormat(SimpleDateFormat dateFormat) {
		this.dateFormat = dateFormat;
	}

}
